package cn.com.guage.dtm.metadata.service.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Optional;
import java.util.Set;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;
import java.util.stream.Collectors;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.com.guage.dtm.common.core.text.Convert;
import cn.com.guage.dtm.common.exception.BusinessException;
import cn.com.guage.dtm.common.utils.DateUtils;
import cn.com.guage.dtm.common.utils.StringUtils;
import cn.com.guage.dtm.metadata.domain.MdField;
import cn.com.guage.dtm.metadata.domain.MdIndex;
import cn.com.guage.dtm.metadata.domain.MdReverseEngineering;
import cn.com.guage.dtm.metadata.domain.MdTable;
import cn.com.guage.dtm.metadata.mapper.MdReverseEngineeringMapper;
import cn.com.guage.dtm.metadata.service.IMdFieldService;
import cn.com.guage.dtm.metadata.service.IMdIndexService;
import cn.com.guage.dtm.metadata.service.IMdReverseEngineeringService;
import cn.com.guage.dtm.metadata.service.IMdTableService;
import cn.com.guage.dtm.utils.AESDecrypt;
import cn.com.guage.dtm.utils.AESEncrypt;

/**
 * 逆向工程Service业务层处理
 * 
 * @author liujun
 * @date 2021-06-25
 */
@Service
public class MdReverseEngineeringServiceImpl implements IMdReverseEngineeringService {

	private static final Logger log = LoggerFactory.getLogger(MdReverseEngineeringServiceImpl.class);


	@Autowired
	private MdReverseEngineeringMapper mdReverseEngineeringMapper;

	@Autowired
	IMdTableService iMdTableService;

	@Autowired
	IMdFieldService iMdFieldService;

	@Autowired
	IMdIndexService iMdIndexService;
	
	//防止清理与抽取冲突
	private Lock lock=new ReentrantLock();
	
	//临时统计字段类型用
//	public static Set dataTypeList= new HashSet();

	/**
	 * 查询逆向工程
	 * 
	 * @param reverseId 逆向工程ID
	 * @return 逆向工程
	 */
	@Override
	public MdReverseEngineering selectMdReverseEngineeringById(Long reverseId) {
		return mdReverseEngineeringMapper.selectMdReverseEngineeringById(reverseId);
	}

	/**
	 * 查询逆向工程列表
	 * 
	 * @param mdReverseEngineering 逆向工程
	 * @return 逆向工程
	 */
	@Override
	public List<MdReverseEngineering> selectMdReverseEngineeringList(MdReverseEngineering mdReverseEngineering) {
		return mdReverseEngineeringMapper.selectMdReverseEngineeringList(mdReverseEngineering);
	}

	/**
	 * 新增逆向工程
	 * 
	 * @param mdReverseEngineering 逆向工程
	 * @return 结果
	 */
	@Override
	public int insertMdReverseEngineering(MdReverseEngineering mdReverseEngineering) {
		mdReverseEngineering.setCreateTime(DateUtils.getNowDate());
		return mdReverseEngineeringMapper.insertMdReverseEngineering(mdReverseEngineering);
	}

	/**
	 * 修改逆向工程
	 * 
	 * @param mdReverseEngineering 逆向工程
	 * @return 结果
	 */
	@Override
	public int updateMdReverseEngineering(MdReverseEngineering mdReverseEngineering) {
		mdReverseEngineering.setUpdateTime(DateUtils.getNowDate());
		return mdReverseEngineeringMapper.updateMdReverseEngineering(mdReverseEngineering);
	}

	/**
	 * 删除逆向工程对象
	 * 
	 * @param ids 需要删除的数据ID
	 * @return 结果
	 */
	@Override
	public int deleteMdReverseEngineeringByIds(String ids) {
		return mdReverseEngineeringMapper.deleteMdReverseEngineeringByIds(Convert.toStrArray(ids));
	}

	/**
	 * 删除逆向工程信息
	 * 
	 * @param reverseId 逆向工程ID
	 * @return 结果
	 */
	@Override
	public int deleteMdReverseEngineeringById(Long reverseId) {
		return mdReverseEngineeringMapper.deleteMdReverseEngineeringById(reverseId);
	}

	@Override
	@Transactional
	public int extractDbMetadata(MdReverseEngineering mdReverseEngineering,boolean isMetaSave) {
		int fetchSize = 0;
		if (lock.tryLock()) {
			// TODO Auto-generated method stub
			if (log.isInfoEnabled()) {
				log.info("begin extractDbMetadata...");
			}
			// new driver class is `com.mysql.cj.jdbc.Driver'
			Connection conn = null;
			ResultSet rs = null;
			ResultSet rsColumn = null;
			ResultSet rsIndex = null;
			String password = mdReverseEngineering.getJdbcPassword();
			if (log.isDebugEnabled())
				log.debug("password before Decry is {}.", password);
			if (isMetaSave) {
				AESDecrypt decry = new AESDecrypt(AESEncrypt.ENCRYPASSWORD);
				password = decry.decryptForCBC(mdReverseEngineering.getJdbcPassword());
				mdReverseEngineering.setJdbcPassword(password);
			}
//    	System.out.println(password);
			if (log.isDebugEnabled())
				log.debug("password after Decry is {}.", password);

			try {
				Class.forName(mdReverseEngineering.getJdbcDriver());
				conn = DriverManager.getConnection(mdReverseEngineering.getJdbcConnString(),
						mdReverseEngineering.getJdbcUsername(), mdReverseEngineering.getJdbcPassword());
				DatabaseMetaData databaseMetaData = conn.getMetaData();
				String[] types = { "TABLE" };
				rs = databaseMetaData.getTables(null, mdReverseEngineering.getJdbcUsername(), "%", types);
				// 如果只是测试，就返回了。
				if (!isMetaSave)
					return 1;
				MdTable mdTable = null;
				while (rs.next()) {
					String tableName = rs.getString("TABLE_NAME");
					if (tableName.startsWith("md")) {
						mdTable = new MdTable();
						mdTable.setTableName(tableName);
						extractTable(rs, mdTable, mdReverseEngineering);

						rsColumn = databaseMetaData.getColumns(null, null, tableName, "%");
						extractField(rsColumn, mdTable);

						// 由于一条index由多条记录组成，所以特殊处理。
						rsIndex = databaseMetaData.getIndexInfo(null, null, tableName, false, false);
						extractIndex(rsIndex, mdTable);
					}
				}
				rs.last();// 这个必须要加，才能得到行数。
				fetchSize = rs.getRow();
			} catch (Exception e) {
				// TODO Auto-generated catch block
//			e.printStackTrace();
				log.error("sql error.", e);
				Optional.ofNullable(rsColumn).ifPresent(rsc -> {
					try {
						rsc.close();
					} catch (SQLException e1) {
						// TODO Auto-generated catch block
						log.error("db recordset close error.", e1);
					}
				});
				Optional.ofNullable(rsIndex).ifPresent(rsi -> {
					try {
						rsi.close();
					} catch (SQLException e1) {
						// TODO Auto-generated catch block
//					e1.printStackTrace();
						log.error("db recordset close error.", e1);
					}
				});
				Optional.ofNullable(rs).ifPresent(rst -> {
					try {
						rst.close();
					} catch (SQLException e1) {
						// TODO Auto-generated catch block
//					e1.printStackTrace();
						log.error("db recordset close error.", e1);
					}
				});
				Optional.ofNullable(conn).ifPresent(co -> {
					try {
						co.close();
					} catch (SQLException e1) {
						// TODO Auto-generated catch block
//					e1.printStackTrace();
						log.error("db connection close error.", e1);
					}
				});
				throw new BusinessException("抽取数据库元数据失败", e);
			} finally {
				lock.unlock();
			}
		}
			if (log.isInfoEnabled())
				log.info("table fetchSize {}.", fetchSize);
			return fetchSize;
	}

	/**
	 * 抽取表
	 * 
	 * @param rs
	 * @param mdTable
	 * @param mdReverseEngineering
	 */
	private void extractTable(ResultSet rs, MdTable mdTable, MdReverseEngineering mdReverseEngineering) {
		try {
//			if (log.isDebugEnabled()) {
//				ResultSetMetaData  resultSetMetaData  = rs.getMetaData();
//				for (int i = 1; i < resultSetMetaData.getColumnCount(); i++) {
//					log.debug(resultSetMetaData.getColumnName(i) + "--->" + rs.getObject(i));
//				}
//			}
			mdTable.setTableCode(rs.getString("TABLE_NAME"));
			mdTable.setTableName(rs.getString("REMARKS"));
			mdTable.setSysId(mdReverseEngineering.getSysId());
			mdTable.setSysCode(mdReverseEngineering.getSysCode());
			mdTable.setSysName(mdReverseEngineering.getSysName());
			iMdTableService.insertMdTable(mdTable);
			if (log.isInfoEnabled()) {
				log.info("find table:" +mdTable.getTableCode());
			}
			//IMdTableService iMdTableService,MdReverseEngineering mdReverseEngineering,MdTable mdTable
			new Thread(new GetTableInfoRunable(iMdTableService,mdReverseEngineering,mdTable)).start();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			log.error("extract table {} error.", mdTable.getTableName());
		}
	}


	/**
	 * 抽取表中的字段
	 * 
	 * @param rsColumn
	 * @param mdTable
	 */
	private void extractField(ResultSet rsColumn, MdTable mdTable) {
		String columnName = "";
		try {
			ResultSetMetaData resultSetMetaData =rsColumn.getMetaData();
			
			while (rsColumn.next()) {
//				if (log.isDebugEnabled()) {
//					for (int i = 1; i < resultSetMetaData.getColumnCount(); i++) {
//						log.debug(resultSetMetaData.getColumnName(i) + "--->" + rsColumn.getObject(i));
//					}
//				}
				MdField mdField = new MdField();
				mdField.setTableId(mdTable.getTableId());
				mdField.setTableCode(mdTable.getTableCode());
				mdField.setTableName(mdTable.getTableName());
				mdField.setFieldCode(rsColumn.getString("COLUMN_NAME"));
				mdField.setFieldName(rsColumn.getString("REMARKS"));
				mdField.setFieldContent(rsColumn.getString("REMARKS"));
				mdField.setFieldType(rsColumn.getLong("DATA_TYPE"));
//				System.out.println("DATA_TYPE:"+rsColumn.getLong("DATA_TYPE"));
//				String typeInfo=rsColumn.getString("TYPE_NAME")+"|"+rsColumn.getLong("DATA_TYPE");
//				dataTypeList.add(typeInfo);
				mdField.setFieldLength(rsColumn.getLong("COLUMN_SIZE"));//总位数
				mdField.setFieldPrecision(rsColumn.getLong("DECIMAL_DIGITS"));//小数精度
				mdField.setFieldCanNull("YES".equals(rsColumn.getString("IS_NULLABLE"))?0L:1L);//可为空
				mdField.setFieldDefault(rsColumn.getString("COLUMN_DEF"));//默认值
				iMdFieldService.insertMdField(mdField);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			if(log.isDebugEnabled())
				log.debug("extract column {} from table {} error.{}", columnName, mdTable.getTableName(),e);
			log.error("extract column {} from table {} error.", columnName, mdTable.getTableName());
		}
	}

	/**
	 * 抽取表中的索引
	 * 
	 * @param rsIndex
	 * @param mdTable
	 */
	private void extractIndex(ResultSet rsIndex, MdTable mdTable) {
		String tableNameIndex = "";
		String indexName = "";
		String columnNameIndex = "";
		try {
//				String asc_descIndex = "";
//				int orderPosition = 0;
			ResultSetMetaData rsMd = rsIndex.getMetaData();
			while (rsIndex.next()) {
//				if (log.isInfoEnabled()) {
//					for (int i = 1; i < rsMd.getColumnCount(); i++) {
//						log.info(rsMd.getColumnName(i) + "--->" + rsIndex.getObject(i));
//					}
//				}
				String _tableNameIndex = rsIndex.getString("TABLE_NAME");
				String _indexName = rsIndex.getString("INDEX_NAME");
				String _columnName = rsIndex.getString("COLUMN_NAME");
//					String _asc_descIndex = rsIndex.getString("ASC_OR_DESC");
//					int _orderPosition = rsIndex.getInt("ORDINAL_POSITION");

				if (_indexName.equals(indexName) && _tableNameIndex.equals(tableNameIndex)) {
					// 表明是同一个index.另外mysql8中才真正实现降序索引。所以先略过，后面看情况测试修改。
					tableNameIndex = _tableNameIndex;
					indexName = _indexName;
					columnNameIndex += StringUtils.isEmpty(columnNameIndex) ? _columnName : "," + _columnName;

				} else {
					// 这是新的索引，之前记录可以存库了。
					if (StringUtils.isNotEmpty(indexName) && !"PRIMARY".equals(indexName)) {
						MdIndex mdIndex = new MdIndex();
						mdIndex.setTableId(mdTable.getTableId());
						mdIndex.setTableCode(mdTable.getTableCode());
						mdIndex.setTableName(mdTable.getTableName());
						mdIndex.setModuleId(mdTable.getModuleId());
						mdIndex.setModuleCode(mdTable.getModuleCode());
						mdIndex.setModuleName(mdTable.getModuleName());
						mdIndex.setSysId(mdTable.getSysId());
						mdIndex.setSysCode(mdTable.getSysCode());
						mdIndex.setSysName(mdTable.getSysName());
						mdIndex.setIndexCode(indexName);
						String createIndexSql = "CREATE INDEX " + mdIndex.getIndexCode() + " ON "
								+ mdIndex.getTableCode() + "(" + columnNameIndex + ")";
						mdIndex.setIndexSql(createIndexSql);
						iMdIndexService.insertMdIndex(mdIndex);
					}
					// 暂存当前的索引
					tableNameIndex = _tableNameIndex;
					indexName = _indexName;
					columnNameIndex = _columnName;
				}
			}
			// 循环结束，如果存的还有数据，就保存一条索引后结束
			if (StringUtils.isNotEmpty(indexName) && !"PRIMARY".equals(indexName)) {
				MdIndex mdIndex = new MdIndex();
				mdIndex.setTableId(mdTable.getTableId());
				mdIndex.setTableCode(mdTable.getTableCode());
				mdIndex.setTableName(mdTable.getTableName());
				mdIndex.setModuleId(mdTable.getModuleId());
				mdIndex.setModuleCode(mdTable.getModuleCode());
				mdIndex.setModuleName(mdTable.getModuleName());
				mdIndex.setSysId(mdTable.getSysId());
				mdIndex.setSysCode(mdTable.getSysCode());
				mdIndex.setSysName(mdTable.getSysName());
				mdIndex.setIndexCode(indexName);
				String createIndexSql = "CREATE INDEX " + mdIndex.getIndexCode() + " ON " + mdIndex.getTableCode() + "("
						+ columnNameIndex + ")";
				mdIndex.setIndexSql(createIndexSql);
				iMdIndexService.insertMdIndex(mdIndex);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			log.error("extract index {} from table {} error.", indexName, mdTable.getTableName());
		}
	}

	@Override
	@Transactional
	public void clearDbMetadataById(Long reverseId) {
		// TODO Auto-generated method stub
		if (lock.tryLock()) {
			try {
				MdReverseEngineering mdReverseEngineering = mdReverseEngineeringMapper
						.selectMdReverseEngineeringById(reverseId);
				MdTable mdTable = new MdTable();
				mdTable.setSysId(mdReverseEngineering.getSysId());
				List<MdTable> tableList = iMdTableService.selectMdTableList(mdTable);
				log.info("exist table number is: {}", tableList.size());
				List<String> tableIds = tableList.stream().map(MdTable::getTableId).map(i -> Long.toString(i))
						.collect(Collectors.toList());

				log.info("exist table is: {}", StringUtils.join(tableIds.toArray(), ","));

				int fields = iMdFieldService.deleteMdFieldByTableIds(StringUtils.join(tableIds.toArray(), ","));
				log.info("exist fields number is {}", fields);

				int tables = iMdTableService.deleteMdTableByIds(StringUtils.join(tableIds.toArray(), ","));
				log.info("exist tables number is {}", tables);

				int indexs = iMdIndexService.deleteMdIndexByTableIds(StringUtils.join(tableIds.toArray(), ","));

				log.info("clear table {}, column {}, index {}.", tables, fields, indexs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				log.error("clear db metadata error {}.", e);
			} finally {
				lock.unlock();
			}
		}
	}
	
	
	public static class GetTableInfoRunable implements Runnable {

		private static final Logger LOGGER = LoggerFactory.getLogger(GetTableInfoRunable.class);

		private MdReverseEngineering _mdReverseEngineering;

		private MdTable _mdTable;
		
		private IMdTableService _iMdTableService;

		public GetTableInfoRunable(IMdTableService iMdTableService,MdReverseEngineering mdReverseEngineering,MdTable mdTable) {
			super();
			_mdReverseEngineering = mdReverseEngineering;
			_mdTable = mdTable;
			_iMdTableService = iMdTableService;
		}

		@Override
		public void run() {
			System.out.println("get table data count thread run....");
			log.info("get table data count thread run....");
			Connection conn=null;
			ResultSet rs =null;
			try {
				if (LOGGER.isDebugEnabled()) {
					LOGGER.debug("_mdTable.getTableCode():" + _mdTable.getTableCode());
//					LOGGER.debug("-------" + _mdReverseEngineering.getJdbcPassword());
				}
				conn = DriverManager.getConnection(_mdReverseEngineering.getJdbcConnString(),
						_mdReverseEngineering.getJdbcUsername(), _mdReverseEngineering.getJdbcPassword());
				Statement primaryStatement = conn.createStatement();
				rs=primaryStatement.executeQuery("select count(*) datanumber from "+_mdTable.getTableCode() );
				if (rs.next()) {
					Long dataNumber = rs.getLong("datanumber");
					LOGGER.info("table {} datanumber is {}", _mdTable.getTableCode(), dataNumber);
					System.out.println(_mdTable.getTableCode() + "datanumber is :" + dataNumber);
					_mdTable.setRecordCount(dataNumber);
					_iMdTableService.updateMdTable(_mdTable);
				}
			} catch (Exception e) {
				// TODO: handle exception
//				e.printStackTrace();
				LOGGER.error("线程抽取表{} 信息出错了。{}",_mdTable.getTableCode(),e);
			} finally {
				Optional.ofNullable(rs).ifPresent(r->{
					try {
						r.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
//						e.printStackTrace();
						log.error("rs close error"+e);
					}
				});
				Optional.ofNullable(conn).ifPresent(c->{
					try {
						c.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						log.error("conn close error"+e);
					}
				});
			}

		}

	}
}
